スプレッドシートと Apps Script でアンケートへ回答してくれた方にお礼メールを一括送信する

クラスメソッドのGoogle Cloud Advent Calendar 2024
#Google Apps Script
#Google Sheets
和田祐介
2024.12.24
こんにちは。Zennチームの和田です。
この記事はクラスメソッドの Google Cloud アドベントカレンダー2024 24日目の記事です。世のすべてのサンタクロースに労いを申し上げます。
さて、Zennは Google Cloud で構成されており、たくさんの恩恵を受けています。今回はGoogle Workspaceの便利な組み合わせについて紹介させてください。
 イベント参加者の方へお礼メールを送りたい先日、クラスメソッドとして Cloud Run のハンズオンイベントを開催させていただきました。
【12/7土 現地開催:渋谷】Cloud Run Dojo - 初級者向けハンズオン - connpass
参加者の方のスキルも高くつつがなく完了しました。参加者の方、チューターの方、ありがとうございました。
このようなイベントでは、Google Form でアンケートを募ることも多いと思います。もちろん connpass などのイベント管理ツールで利用できるアンケート機能もありますが、社内での共有やグラフ化といったタスクを担える点、Googleのエコシステムの恩恵は魅力的です。
本稿では、「アンケートへ答えてくれた方に、お礼メールを送りたい」という状況を考えてみます。
 確認メッセージを編集する本当に固定文言でよければ、Google Form の確認メッセージを編集するのもひとつの手です。
このケースにおさまらず、内容を確認して返信する内容を少し変えたり、お礼メールを送る・送らないを制御したい場合は、Apps Scriptを使う方法もあります。
 スプレッドシートの情報とApps Scriptを使いメール送信もちろん一通ずつGmailからメールを送るでもまったく問題ありません。ただ、中規模以上のイベントで、たとえば30人以上にお礼を送るとなると、少し大変かもしれません。そこで、アンケートでメールアドレスと名前を収集し、その情報を使って Apps Script からメールを送信してみます。以下ではその方法について記載します。
 Google Form でメールアドレスを収集するようにメールアドレスを収集する必要があるので、設定から「メールアドレスを収集する」とします。
 スプレッドシートへリンク「回答」タブから、スプレッドシートへリンクし、回答内容が蓄積されるよう設定します。
 アンケートフォームではお名前を必須にするとよいかもメール本文で使います。
 回答を集めるイベント当日、アンケートを募ってください。
イベントページ（connpass）に Google Form のURLを載せる
イベントのクロージングでデカデカとスライドへQRコードを表示する
ハッシュタグとともにXにポストする
このあたりが鉄板でしょうか。
 スプレッドシートでメール送信用のシートを作るいま、5件の回答が集まった状況を想定しています。
Google Form の回答はスプレッドシートへ記録されますが、このシートはあまり触りたくありません。別のシートを用意して、そこでメール一括送信を管理します。「メール送信用」としました。
 データを埋めるメールの送信に必要なデータを埋めます。
 メールアドレスメールアドレスは回答を参照します。次の関数を利用するのがおすすめです。
=INDIRECT("'フォームの回答 2'!B"&ROW())
普通に='フォームの回答 2'!B2と参照すればいいのでは？というところですが、Google Form に回答があったときの動きは、どうやらスプレッドシートへのデータ挿入のようです。この場合、スプレッドシートの関数が気を利かして、挿入されたデータは参照しないようになってしまいます。回答募集中のアンケートなど、データが挿入される可能性がある場合、直接参照するよりもINDIRECTで文字列参照するほうがよさそうでした。
ためしに両方設定して、ooedo.kanako@example.comさんが追加でアンケート回答してみます。
セルをみると ='フォームの回答 2'!B7 を参照してほしいところ、ひとつとばして ='フォームの回答 2'!B8 を参照してしまいっています。データ挿入により関数の参照も飛ばしてくれているみたいですね。今回はこの動きだと都合が悪いのでINDIRECT関数を応用しました。
 名前メールアドレスが参照できたのでVLOOKUPを使います。
=VLOOKUP(B2,'フォームの回答 2'!B:C,2,FALSE)
B2（メールアドレス）の値を使い、回答フォームのB列C列を対象に検索、ヒットした行の2列目の値（お名前）を完全一致で取得します。
 「送信済み」フラグを追加一度送信したレコードをマーキングするため、A列は「送信済み」フラグとします。これはApps Scriptから更新します。列を選択して挿入＞チェックボックスとすると楽です。
 送信元、タイトル、メール本文次にメールを送るための情報です。本文には回答者の方の名前を入れたいので、テンプレート本文を用意し、部分的に置き換える作戦でいきます。「メール情報」シートを作成してください。
送信元とタイトルは固定参照でOKです。参照にしておくと、一括で変更する場合などに楽です。
送信元: ='メール情報'!$B$1
タイトル: ='メール情報'!$B$2
そして名前入り本文は、テンプレート文字列{{}}とSUBSTITUTE関数で実装できます。今回は{{name}}などとしてますが、置き換え対象が本文から見つけられればなんでもOKです。
{{name}} 様

この度は、「Cloud Run Dojo」にご参加いただき、誠にありがとうございました！
イベントを通じて、Google Cloud の Cloud Run に関する知識や実践的なスキルを深めていただけたなら幸いです。

皆様の熱意ある参加と多くのご質問のおかげで、私たち運営チームにとっても充実した時間となりました。心より感謝申し上げます。

Cloud Run Dojo が、皆様の開発効率を向上させ、新たな可能性を切り開くきっかけになれば嬉しいです。今後も Google Cloud を活用していただければ幸いです。

何かご不明点やご質問がございましたら、いつでもお気軽にお問い合わせください！
これを使ってname部分を置き換えます。
本文: =SUBSTITUTE('メール情報'!$B$3, "{{name}}", D2)
{{name}} を D2 の値（名前）で置き換えてくれます。なお吉村圭一郎さんに関しては、「懇親会で出た地ビールは産地どこでしたっけ？」という質問をもらっているため、関数ではなく値にしてしまい、個別に編集します。
 一括送信の Apps Script を書くこれで準備ができたので Apps Script を書いていきます。拡張機能＞Apps Scriptを選択してください。次のコードを記載します。
function sendEmails() {
  // スプレッドシートを取得
  const activeSheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = activeSheet.getSheetByName('メール送信用');

  // シート内のすべてのデータを取得
  const data = sheet.getDataRange().getValues();

  // ヘッダー行をスキップ
  for (let i = 1; i < data.length; i++) { // i = 1 でヘッダーをスキップ
    const row = data[i];
    const sent = row[0]; // 送信済み 列

    const toEmail = row[1]; //メールアドレス 列
    const fromEmail = row[4]; // 送信元 列
    const subject = row[5]; // タイトル 列
    const bodyText = row[6];     // 本文 列

    // sent が FALSE のみ処理する
    if (sent === true) {
      Logger.log(`送信済みであるためスキップします: ${toEmail}`)
      continue;
    }

    // 有効なメールアドレスでない場合はスキップ
    if (!isValidEmail(toEmail)) {
      Logger.log(`有効なメールアドレスではないためスキップします: ${toEmail}`)
      continue;
    }

    // 空のデータがある場合はスキップ
    if (!subject || !fromEmail || !bodyText) {
      Logger.log(`空のデータがあるためスキップします: ${toEmail}`)
      continue;
    }

    try {
      const options = {
        from: fromEmail
      }
      // Gmail を使ってメールを送信
      // GmailApp.sendEmail(toEmail, subject, bodyText, options);
      Logger.log({
        toEmail,
        subject,
        bodyText,
        options
      });

      // メール送信後に sent 列を TRUE に更新
      sheet.getRange(i + 1, 1).setValue(true); // i+1 は1行目がヘッダーのため
      Logger.log(`メールを送信しました: ${toEmail}`);
    } catch (error) {
      Logger.log(`Failed to send email to ${toEmail}: ${error.message}`);
    }

  }

  Logger.log("メール送信が完了しました");
}

function isValidEmail(emailAddress) {
  var pattern = /^[a-zA-Z0-9._-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,4}$/;
  return pattern.test(emailAddress);
}

やっていることはシンプルで、スプレッドシートのAPIを使いデータを取得、行ごとに処理してメールを送信しています。テストのためコメントアウトしていますが GmailApp.sendEmail(toEmail, subject, bodyText, options); でGmailアプリから送るのと同じようにメールが送信できます。
起動は、メニューの「実行」から行えます。
吉村さんには個別に設定した本文が送信されていることが確認できます。
さらに、送信後、
      // メール送信後に sent 列を TRUE に更新
      sheet.getRange(i + 1, 1).setValue(true); // i+1 は1行目がヘッダーのため
この処理により送信済みのチェックボックスがTRUEになります。これで、もう一度実行しても送信されることはありません。
 おわりにGoogle Form からのアンケート回答に対して、Apps Scriptを使い一括でメールを送信しました。メールアドレスと名前さえスプレッドシートに集められれば、同じように一括でメール送信できます。とくにワンショットのイベントで真価を発揮するかもしれません。
 余談: 認可の統合がFaaSに求めていたモノかもしれないApps Script は、Webブラウザで関数を編集・実行できるサービスですが、スクリプトがスプレッドシートやGmailのAPIにアクセスするため、認可が必要です。はじめて実行する場合、以下のように確認が求められます。
スクリプト実行の仕組みを自分でたてようとすると、スプレッドシートおよびGmailへアクセスするためのトークンを取得し…となるわけですが、Apps Script の場合はGoogleアカウントの選択・認可をシームレスに行えます。Web上で関数を実行する場合、別のシステムを呼び出すケースも多いはずです。Googleという信頼できるクラウドベンダーが、業務アプリへの認可を管理してくれた上でスクリプト（関数）を実行できるのは心強いです。同時に、クラウド上のFaaSの目的が別サービスの呼び出しならば、人間が最小限の権限を付与するのではなく、コードから必要な権限を割り出して認可を求めるほうが体験が良いと思いました。
 参考Gmail と Google スプレッドシートでメールへの差し込みを作成する  |  Apps Script  |  Google for Developers